package model;

import java.io.BufferedReader;
import java.net.HttpRetryException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.google.gson.JsonPrimitive;

import dto.TweetObj;

public class Cube {
	

	public JsonObject getAll(Connection conn) throws Exception {
		Gson gson = new Gson();

		String sql = "SELECT sum(count) FROM cube.facttable";
		PreparedStatement stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		
		JsonArray categories = new JsonArray();

		//JsonArray dataSeries = new JsonArray();
		JsonObject ret = new JsonObject();

		//ArrayList<String> cates = new ArrayList<String>();
		JsonArray countList = new JsonArray();
		JsonArray seriesArray = new JsonArray();

		
		
		while (rs.next()) {
			//JsonPrimitive cate = new JsonPrimitive(rs.getString(1));
			JsonPrimitive count = new JsonPrimitive(Integer.valueOf(rs
					.getString(1)));
			//counterList.add(Integer.valueOf(rs.getString(2)));
			categories.add(new JsonPrimitive("All"));
			countList.add(count);
		}

		// series.addAll(countList);

		JsonObject sobj = new JsonObject();
		// sobj.add(property, value);

		JsonParser parser = new JsonParser();

		sobj.addProperty("name", "count");
		sobj.add("data", countList);

		ret.addProperty("title", "ALL");
		ret.addProperty("subtitle", "ALL");
		//ret.addProperty("entropy", entropy);
		ret.add("categories", categories);
		seriesArray.add(sobj);
		ret.add("series", seriesArray);

		System.out.println(ret.toString());
		//System.out.println("group " + sqlStr);
		//System.out.println("Entropy " + entropy);

		return ret;

	}

	public JsonObject groupTweets(Connection conn, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		String level0 = request.getParameter("level0");
		String level1 = request.getParameter("level1");
		String level2 = request.getParameter("level2");

		String country = request.getParameter("country");
		String city = request.getParameter("city");

		String year = request.getParameter("year");
		String month = request.getParameter("month");
		String day = request.getParameter("day");

		String level0Sel = request.getParameter("level0Sel");
		String level1Sel = request.getParameter("level1Sel");
		String level2Sel = request.getParameter("level2Sel");

		String countrySel = request.getParameter("countrySel");
		String citySel = request.getParameter("citySel");

		String yearSel = request.getParameter("yearSel");
		String monthSel = request.getParameter("monthSel");
		String daySel = request.getParameter("daySel");

		Map<String, String> parameterPair = new HashMap<String, String>();

		String title = "";
		String subTitle = "";

		boolean level = false;
		boolean loc = false;
		boolean date = false;

		if (!(level0.equals("none") && level1.equals("none")
				&& level2.equals("none") && level0Sel.equals("false")
				&& level1Sel.equals("false") && level2Sel.equals("false"))) {
			level = true;
		}

		if (!(country.equals("none") && city.equals("none")
				&& countrySel.equals("false") && citySel.equals("false"))) {
			loc = true;
		}

		if (!(year.equals("none") && month.equals("none") && day.equals("none")
				&& yearSel.equals("false") && monthSel.equals("false") && daySel
					.equals("false"))) {

			date = true;
		}

		boolean firstCol = true;
		StringBuffer sb = new StringBuffer("select ");

		if (level0Sel.equals("true")) {
			sb.append("checkintable.level0Label");
			firstCol = false;
		}

		if (level1Sel.equals("true")) {
			if (!firstCol) {
				sb.append(",");
			}
			sb.append("checkintable.level1Label");
			firstCol = false;
		}

		if (level2Sel.equals("true")) {
			if (!firstCol) {
				sb.append(",");
			}
			sb.append("checkintable.level2Label");
			firstCol = false;
		}

		if (countrySel.equals("true")) {
			if (!firstCol) {
				sb.append(",");
			}
			sb.append("loctable.country");
			firstCol = false;
		}
		if (citySel.equals("true")) {
			if (!firstCol) {
				sb.append(",");
			}
			sb.append("loctable.city");
			firstCol = false;
		}

		if (yearSel.equals("true")) {
			if (!firstCol) {
				sb.append(",");
			}
			sb.append("datetable.year");
			firstCol = false;
		}

		if (monthSel.equals("true")) {
			if (!firstCol) {
				sb.append(",");
			}
			sb.append("datetable.month");
			firstCol = false;
		}
		if (daySel.equals("true")) {
			if (!firstCol) {
				sb.append(",");
			}
			sb.append("datetable.day");
			firstCol = false;
		}
		
		boolean noGroup  = true;
		
		if(level0Sel.equals("true")||level1Sel.equals("true")||level2Sel.equals("true")||citySel.equals("true")||countrySel.equals("true")||yearSel.equals("true")||monthSel.equals("true")||daySel.equals("true")){
			noGroup = false;
			sb.append(",");
		}
		sb.append("sum(count) as count from facttable");
		

		if (level) {
			sb.append(",checkintable ");
		}
		if (loc) {
			sb.append(",loctable ");
		}
		if (date) {
			sb.append(",datetable ");
		}
		sb.append("where ");
		boolean andFlag = false;

		if (level) {
			sb.append("facttable.checkId=checkintable.checkInId");
			if (!level0.equals("none")) {
				sb.append(" and checkintable.level0Label=" + "\'" + level0
						+ "\'");
				title += (" Level0=" + level0);

				parameterPair.put("level0", level0);
			}
			if (!level1.equals("none")) {
				sb.append(" and checkintable.level1Label=" + "\'" + level1
						+ "\'");
				title += (" Level1=" + level1);

				parameterPair.put("level1", level1);
			}
			if (!level2.equals("none")) {
				sb.append(" and checkintable.level2Label=" + "\'" + level2
						+ "\'");
				title += (" Level2=" + level2);

				parameterPair.put("level2", level2);
			}
			andFlag = true;
		}

		if (loc) {
			if (andFlag) {
				sb.append(" and ");
			}
			sb.append("facttable.locId=loctable.locId");
			if (!country.equals("none")) {
				sb.append(" and loctable.country like" + "\'" + country + "\'");
				title += (" Country=" + country);

				parameterPair.put("country", country);
			}
			if (!city.equals("none")) {
				sb.append(" and loctable.city like" + "\'" + city + "\'");
				title += (" City=" + city);

				parameterPair.put("city", city);
			}

			andFlag = true;
		}

		if (date) {
			if (andFlag) {
				sb.append(" and ");
			}
			sb.append("facttable.dateId=datetable.dateId");
			if (!year.equals("none")) {
				sb.append(" and dateTable.year like" + "\'" + year + "\'");
				title += (" Year=" + year);

				parameterPair.put("year", year);
			}
			if (!month.equals("none")) {
				sb.append(" and dateTable.month like" + "\'" + month + "\'");
				title += (" Month=" + month);

				parameterPair.put("month", month);
			}
			if (!day.equals("none")) {
				sb.append(" and dateTable.day like" + "\'" + day + "\'");
				title += (" Day=" + day);

				parameterPair.put("day", day);
			}

			andFlag = true;
		}

		StringBuffer gySb = new StringBuffer();
		
		if(noGroup==false){
			gySb.append(" group by ");
		}
		

		boolean firstGroup = true;

		if (level0Sel.equals("true")) {
			gySb.append("checkintable.level0Label");
			firstGroup = false;
			subTitle += ("Group by Level0");

			parameterPair.put("Group", "level0");
		}

		if (level1Sel.equals("true")) {
			if (!firstGroup) {
				gySb.append(",");
			}
			firstGroup = false;
			gySb.append("checkintable.level1Label");
			subTitle += ("Group by Level1");

			parameterPair.put("Group", "level1");
		}
		if (level2Sel.equals("true")) {
			if (!firstGroup) {
				gySb.append(",");
			}
			firstGroup = false;

			gySb.append("checkintable.level2Label");
			subTitle += ("Group by Level2");

			parameterPair.put("Group", "level2");
		}
		if (countrySel.equals("true")) {
			if (!firstGroup) {
				gySb.append(",");
			}
			firstGroup = false;

			gySb.append("loctable.country");
			subTitle += ("Group by Country");

			parameterPair.put("Group", "country");
		}
		if (citySel.equals("true")) {
			if (!firstGroup) {
				gySb.append(",");
			}
			firstGroup = false;

			gySb.append("loctable.city");
			subTitle += ("Group by City");

			parameterPair.put("Group", "city");
		}
		if (yearSel.equals("true")) {
			if (!firstGroup) {
				gySb.append(",");
			}
			firstGroup = false;

			gySb.append("datetable.year");
			subTitle += ("Group by Year");

			parameterPair.put("Group", "year");
		}
		if (monthSel.equals("true")) {
			if (!firstGroup) {
				gySb.append(",");
			}
			firstGroup = false;

			gySb.append("datetable.month");
			subTitle += ("Group by Month");

			parameterPair.put("Group", "month");
		}
		if (daySel.equals("true")) {
			if (!firstGroup) {
				gySb.append(",");
			}
			firstGroup = false;

			gySb.append("datetable.day");
			subTitle += ("Group by Day");

			parameterPair.put("Group", "day");
		}

		String sqlStr = sb.toString() + gySb.toString();
		System.out.println("Gstr"+sqlStr);
		PreparedStatement stmt = conn.prepareStatement(sqlStr);

		ResultSet rs = stmt.executeQuery();

		JsonArray categories = new JsonArray();

		JsonArray dataSeries = new JsonArray();
		JsonObject ret = new JsonObject();

		ArrayList<String> cates = new ArrayList<String>();
		JsonArray countList = new JsonArray();
		JsonArray seriesArray = new JsonArray();

		double entropy = 0.0;
		int sum = 0;
		ArrayList<Integer> counterList = new ArrayList<Integer>();

		Gson gson = new Gson();

		if(noGroup){
			while (rs.next()) {
				JsonPrimitive cate = new JsonPrimitive("Count");
				int nCount = 0;
				if(rs.getString(1)!=null){
					nCount = Integer.valueOf(rs.getString(1));
				}
				JsonPrimitive count = new JsonPrimitive(nCount);
				
				sum += nCount;
				counterList.add(nCount);
				categories.add(cate);
				countList.add(count);
			}
		}
		else{
			while (rs.next()) {
				JsonPrimitive cate = new JsonPrimitive(rs.getString(1));
				JsonPrimitive count = new JsonPrimitive(Integer.valueOf(rs
						.getString(2)));
				sum += Integer.valueOf(rs.getString(2));
				counterList.add(Integer.valueOf(rs.getString(2)));
				categories.add(cate);
				countList.add(count);
			}
		}
		
		

		entropy = getEntropy(counterList, sum);

		// series.addAll(countList);

		JsonObject sobj = new JsonObject();
		// sobj.add(property, value);

		JsonParser parser = new JsonParser();

		sobj.addProperty("name", "count");
		sobj.add("data", countList);

		ret.addProperty("title", title);
		ret.addProperty("subtitle", subTitle);
		ret.addProperty("entropy", entropy);
		ret.add("categories", categories);
		seriesArray.add(sobj);
		ret.add("series", seriesArray);

		System.out.println(ret.toString());
		System.out.println("group " + sqlStr);
		System.out.println("Entropy " + entropy);

		return ret;

	}

	public ArrayList<TweetObj> getTweets(Connection conn,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {

		// Gson gson = new Gson();
		// JsonParser parser = new JsonParser();
		// BufferedReader bf = request.getReader();
		String str = null;
		JsonObject jsonobj = null;
		// if((str =bf.readLine())!=null){
		// jsonobj = parser.parse(str.trim()).getAsJsonObject();
		// }

		System.out.println("Cube Test");

		String level0 = request.getParameter("level0");
		System.out.println(level0);
		String level1 = request.getParameter("level1");
		System.out.println(level1);

		String level2 = request.getParameter("level2");
		System.out.println(level2);

		String country = request.getParameter("country");
		System.out.println(country);

		String city = request.getParameter("city");
		System.out.println(city);

		String year = request.getParameter("year");
		System.out.println(year);

		String month = request.getParameter("month");
		System.out.println(month);

		String day = request.getParameter("day");
		System.out.println(day);

		StringBuffer sb = new StringBuffer(
				"select facttable.tweets from facttable");

		System.out.println("sqlstr test" + sb.toString());

		boolean checkIn = false;
		boolean loc = false;
		boolean date = false;

		if (!level0.equals("none") || !level1.equals("none")
				|| !level2.equals("none")) {
			checkIn = true;
			sb.append(",checkintable");

		}

		if (!country.equals("none") || !city.equals("none")) {
			loc = true;
			sb.append(",loctable");
		}

		if (!year.equals("none") || !month.equals("none")
				|| !day.equals("none")) {
			date = true;
			sb.append(",datetable");
		}

		if (!(checkIn == false && loc == false && date == false)) {
			sb.append(" where ");
		}

		boolean andFlag = false;
		if (checkIn) {
			sb.append("facttable.checkId=checkintable.checkInId");
			if (!level0.equals("none")) {
				sb.append(" and checkintable.level0Label like" + "\'" + level0
						+ "\'");
			}
			if (!level1.equals("none")) {
				sb.append(" and checkintable.level1Label like" + "\'" + level1
						+ "\'");
			}
			if (!level2.equals("none")) {
				sb.append(" and checkintable.level2Label like" + "\'" + level2
						+ "\'");
			}
			andFlag = true;
		}

		if (loc) {
			if (andFlag) {
				sb.append(" and ");
			}
			sb.append("facttable.locId=loctable.locId");
			if (!country.equals("none")) {
				sb.append(" and loctable.country like" + "\'" + country + "\'");
			}
			if (!city.equals("none")) {
				sb.append(" and loctable.city like" + "\'" + city + "\'");
			}

			andFlag = true;
		}

		if (date) {
			if (andFlag) {
				sb.append(" and ");
			}
			sb.append("facttable.dateId=datetable.dateId");
			if (!year.equals("none")) {
				sb.append(" and dateTable.year like" + "\'" + year + "\'");
			}
			if (!month.equals("none")) {
				sb.append(" and dateTable.month like" + "\'" + month + "\'");
			}
			if (!day.equals("none")) {
				sb.append(" and dateTable.day like" + "\'" + day + "\'");
			}

			andFlag = true;
		}

		String sqlStr = sb.toString();
		System.out.println("sqlstr" + sqlStr);

		try {
			ArrayList<TweetObj> tList = new ArrayList<TweetObj>();
			PreparedStatement selectStmt = conn.prepareStatement(sqlStr);
			System.out.println(sqlStr);
			ResultSet rs = selectStmt.executeQuery(sqlStr);
			while (rs.next()) {

				String[] tweetsList = rs.getString(1).split(",");

				for (String tid : tweetsList) {
					String tsql = "select tweet,locLL from cube.idtotweets where id="
							+ "\'" + tid + "\'";
					PreparedStatement tweetStmt = conn.prepareStatement(tsql);
					ResultSet rst = tweetStmt.executeQuery();
					if (rst.next()) {
						String tweet = rst.getString(1);
						String locLL = rst.getString(2);

						if (locLL == null) {
							TweetObj tobj = new TweetObj(tid, tweet, null, null);
							tList.add(tobj);
							continue;
						}
						String[] locLLs = locLL.split(",");
						// System.out.println(tweet);
						TweetObj tobj = new TweetObj(tid, tweet, locLLs[1],
								locLLs[0]);
						tList.add(tobj);
					}

				}

			}

			return tList;

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();

			System.out.print(e.getMessage());
		}

		return null;

	}

	private double getEntropy(ArrayList<Integer> list, int sum) {
		double ret = 0.0;
		for (Integer num : list) {
			double se = (double) num / sum * Math.log((double) num / sum);
			se /= Math.log(2.0) * (-1.0);
			ret += se;
		}
		return ret;
	}

}
